package com.softer.chengxin.person.dao;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.person.dao.provider.PersonDaoProvider;
import com.softer.chengxin.person.domin.Person;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2018/9/26
 */
@Mapper
@Repository
public interface PersonDao {
    /**
     * 查询企业主要人员
     *
     * @param id   企业ID
     * @param duty 职务
     * @return 人员信息
     */
    @Select("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties,state," +
            "project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday," +
            "country_code as countryCode,education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone," +
            "political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major,insurance,certificate,id_card_file_id as idCardFileId," +
            "certificate_file_id as certificateFileId,social_security_file_id as socialSecurityFileId," +
            "labor_contract_file_id as laborContractFileId,diploma_file_id as diplomaFileId" +
            " FROM company_person WHERE company_id = #{id} AND duties =#{duty} and state=1 limit 1")
    Person queryMainPersonnel(@Param("id") Integer id, @Param("duty") String duty);

    /**
     * 增加人员
     *
     * @param person 人员实体
     * @return 增加结果
     */
    @Insert("INSERT INTO company_person(gmt_create,gmt_modified ,person_id,project_id ,company_id ,department_id,`name`,sex,birthday," +
            "country_code,education_level ,id_card,photo,phone,political_status,major,insurance,duties,state,certificate)" +
            " VALUES(now(),now(),#{p.personId},#{p.projectId},#{p.companyId},#{p.departmentId},#{p.name},#{p.sex},#{p.birth},#{p.countryCode}," +
            "#{p.educationLevel},#{p.idCard},#{p.photo},#{p.phone},#{p.politicalStatus},#{p.major},#{p.insurance},#{p.duties},1,#{p.certificate})")
    int addPerson(@Param("p") Person person);

    /**
     * 判断该身份证是否存在
     *
     * @param idCard 身份证
     * @return 身份证
     */
    @Select("SELECT id_card FROM company_person WHERE id_card=#{idCard} and state=1 limit 1")
    String selectPerson(@Param("idCard") String idCard);

    /**
     * 根据身份证查询人员
     *
     * @param idCard 身份证
     * @return 人员信息
     */
    @Select("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties as dutiesCode,(SELECT name FROM dic_info WHERE code = duties) as duties,state," +
            "project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday," +
            "country_code as countryCode,education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone," +
            "political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major,insurance,certificate FROM company_person WHERE id_card = #{id} and state=1")
    Person getPersonByIdCard(@Param("id") String idCard);

    /**
     * 判断该企业下是否存在该身份证
     *
     * @param idCard 身份证
     * @param id     企业ID
     * @return 身份证
     */
    @Select("SELECT id_card FROM company_person WHERE id_card=#{idCard} and state=1 and company_id=#{id} limit 1")
    String checkCorpPerson(@Param("idCard") String idCard, @Param("id") String id);

    /**
     * 判断身份证的职务
     *
     * @param idCard 身份证
     * @return 职务
     */
    @Select("SELECT duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties FROM company_person WHERE id_card=#{idCard} and state=1 limit 1")
    String checkDuty(@Param("idCard") String idCard);

    /**
     * 物业人员信息编辑
     *
     * @param person 人员实体
     * @return 修改是否成功
     */
    @Update("update company_person set `name`=#{p.name},sex=#{p.sex},country_code=#{p.countryCode}," +
            "birthday=#{p.birth},education_level=#{p.educationLevel},id_card=#{p.idCard},photo=#{p.photo}," +
            "political_status=#{p.politicalStatus},phone=#{p.phone},department_id=#{p.departmentId}," +
            "major=#{p.major},insurance=#{p.insurance},duties=#{p.duties},certificate=#{p.certificate} where id=#{p.id}")
    int updatePerson(@Param("p") Person person);


    /**
     * 项目人员信息编辑
     *
     * @param person 项目人员实体
     * @return 修改是否成功
     */
    @Update("update company_person set `name`=#{p.name},sex=#{p.sex},country_code=#{p.countryCode},project_id =#{p.projectId}," +
            "birthday=#{p.birth},education_level=#{p.educationLevel},id_card=#{p.idCard},photo=#{p.photo}," +
            "political_status=#{p.politicalStatus},phone=#{p.phone},department_id=#{p.departmentId},person_id=#{p.personId}," +
            "major=#{p.major},insurance=#{p.insurance},duties=#{p.duties},certificate=#{p.certificate} where id=#{p.id}")
    int updateProjectPerson(@Param("p") Person person);

    /**
     * 查询企业从业人员信息数量
     *
     * @param id           企业ID
     * @param name         人员名字
     * @param departmentId 部门ID
     * @return 人员数量
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryPersonListNum")
    int queryPersonListNum(@Param("id") Integer id, @Param("name") String name,
                           @Param("departmentId") Integer departmentId);

    /**
     * 分页查询企业人员列表
     *
     * @param page         页面信息
     * @param id           企业ID
     * @param name         人员名字
     * @param departmentId 部门ID
     * @return 人员列表
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryPersonList")
    List<Person> queryPersonList(@Param("page") Page page, @Param("id") Integer id,
                                 @Param("name") String name, @Param("departmentId") Integer departmentId);

    /**
     * 根据Id查询人员信息
     *
     * @param id 人员ID
     * @return 人员信息
     */
    @Select("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties,state," +
            "project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday," +
            "country_code as countryCode,education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone," +
            "political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major,insurance,certificate FROM company_person WHERE id = #{id}")
    Person queryPersonById(@Param("id") Integer id);

    /**
     * 刪除人员（将人员状态设为离职,离职状态设置为0）
     *
     * @param id 人员Id
     * @return 处理结果
     */
    @Update("UPDATE company_person SET state=0 WHERE id=#{id}")
    int delete(@Param("id") Integer id);

    /**
     * 彻底删除从业人员
     *
     * @param id 人员ID
     * @return 处理结果
     */
    @Delete("DELETE FROM company_person WHERE id=#{id}")
    int deletePerson(@Param("id") Integer id);

    /**
     * 删除项目人员
     *
     * @param id 人员ID
     * @return 处理结果
     */
    @Update("UPDATE company_person SET project_id=0 WHERE id=#{id}")
    int updatePersonProject(@Param("id") Integer id);

    /**
     * 删除项目时，删除项目中的人员
     *
     * @param projectId 项目ID
     * @return 删除数量
     */
    @Update("delete from company_person WHERE project_id=#{projectId}")
    int deleteProjectPerson(@Param("projectId") Integer projectId);

    /**
     * 查询企业所有人员，用于下拉列表
     *
     * @param name 姓名，用于模糊查询
     * @param id   企业ID
     * @return 人员列表
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryAllPerson")
    List<Person> queryAllPerson(@Param("name") String name, @Param("id") Integer id);

    /**
     * 查询项目人员数量
     *
     * @param person 查询实体
     * @return 人员数量
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryProjectPersonNum")
    int queryProjectPersonNum(@Param("person") Person person);

    /**
     * 查询项目人员列表
     *
     * @param page   页面实体
     * @param person 查询实体
     * @return 人员列表
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryProjectPersonList")
    List<Person> queryProjectPersonList(@Param("page") Page page, @Param("person") Person person);


}
